package com.kb.design.mapper.postgreSQL;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.kb.design.entity.DesignPost;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 订单表(DesignOrder)表数据库访问层
 *
 * @author makejava
 * @since 2023-11-24 10:56:46
 */
@Mapper
public interface DesignPostMapper extends BaseMapper<DesignPost> {
    @Select("select count(1) from design_user_post where post_id = #{postId} and user_id = #{userId}")
    public int countUserPostById(Long postId);

    @Select("SELECT\n" +
            "\tdesign_post.* \n" +
            "FROM\n" +
            "\t\"design_post\"\n" +
            "\tLEFT JOIN design_user_post ON design_post.post_id = design_user_post.post_id\n" +
            "\tLEFT JOIN design_user ON design_user.\"id\" = design_user_post.user_id \n" +
            "WHERE\n" +
            "\tdesign_user.code = #{userCode}")
    public List<DesignPost> queryPostUser(String userCode);

    /**
     * 查询各个userid的岗位目标
     */
    @Select("SELECT u.user_id as postId,p.pin_target FROM design_user_post u LEFT JOIN design_post p ON u.post_id = p.post_id")
    public List<DesignPost> queryPostUserPinTarget();
    @Select({
            "<script>",
            "SELECT p.*,u.target_pin FROM design_post p",
            "LEFT JOIN design_user_post up ON up.post_id = p.post_id",
            "LEFT JOIN design_user u ON u.id = up.user_id",
            "WHERE u.code = #{code}",
            "AND u.del_flag = '0'",
            "LIMIT 1", // 只取一条记录
            "</script>"
    })
    public DesignPost getPostByUserCode(String code);
}

